﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.project.dao.impl;

import java.util.*;

import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.project.dao.*;
import com.hh.bss.project.entity.*;
import com.hh.bss.project.service.*;
import com.hh.bss.project.vo.query.*;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class ContractJobCvInterviewDaoImpl extends BaseHibernateDao<ContractJobCvInterview,java.lang.Integer>  implements ContractJobCvInterviewDao<ContractJobCvInterview,java.lang.Integer>{

	
	public Class getEntityClass() {
		return ContractJobCvInterview.class;
	}
	public Page findJobCvInterviewPage(ContractJobCvInterviewQuery query){
		//人选姓名，职位，客户职位联系人，面试日期，项目负责人，信息录入人，手机，电邮，客户公司  结果 计划时间
		StringBuffer sql = new StringBuffer();		
		sql.append(" select * from ( ");		
		sql.append(" select a.id,a.cv_id cvId,a.job_id jobId,a.recomId,b.cv_name cvName,a.name jobName,d.cv_name jobContacter,a.plan_dt planDt,a.iv_dt ivDt,a.result,a.e.account owner,a.interviewer interviewer,a.interviewer_title interviewerTitle,b1.name custName,a.creator,b.mobile,b.tel phone,b.email,b.company cvCompany ,b.title cvTitle,a.create_dt createDt,a.modified_dt modifiedDt  from (select distinct a1.id,a1.creator,a1.plan_dt,a1.iv_dt,a1.result,a1.interviewer,a1.interviewer_title,a0.cv_id,a0.job_id,a0.id recomId,a2.name,a2.hr_contacter,a2.ar_contacter,a2.cust_id,a1.create_dt,a1.modified_dt from contract_job_cv_recom a0,contract_job_cv_interview a1,contract_job a2 where a0.id=a1.recom_id and a0.job_id=a2.id) a ");
		sql.append(" left join  cv b on a.cv_id=b.id and b.is_delete=0 ");
		//customer_contacter
		sql.append(" left join  (select t2.id,t1.name,t1.full_name from company t1,customer t2 where t1.id=t2.company_id ) b1 on a.cust_id=b1.id ");
		//sql.append(" left join  (select a0.id,a0.cv_name,a1.role,a1.cust_id from cv a0,customer_contacter a1 where a0.id=a1.cv_id ) d on a.cust_id=d.cust_id and d.role='").append(ConstantDic.CUSTOMER_CONTACTER_TYPE_JOB_CONTACTER).append("' ");
		sql.append(" left join  (select  group_concat((a0.cv_name) separator ',') cv_name,a1.cust_id from cv a0,customer_contacter a1 where a0.id=a1.cv_id  and a1.role='").append(ConstantDic.CUSTOMER_CONTACTER_TYPE_JOB_CONTACTER).append("' group by a1.cust_id   ) d on a.cust_id=d.cust_id ");
		sql.append(" left join (select  t1.job_id,min(t2.account) account from   contract_job_search_project t1,search_project t2 where t1.project_id=t2.id and t1.job_id=? group by t1.job_id ) e on a.job_id=e.job_id ");		
		//sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as mobile from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_MOBILE).append("' group by cv_id) f on a.cv_id=f.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((concat(if(nbr_ext1 is null,'','('),ifnull(nbr_ext1,''),if(nbr_ext1 is null,'',')'),ifnull(nbr,''),if(nbr_ext2 is null,'','-'),ifnull(nbr_ext2,''))) separator ',') as phone from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_TEL).append("' group by cv_id) h on a.cv_id=h.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as email from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_EMAIL).append("' group by cv_id) i on a.cv_id=i.cv_id ");
		//sql.append(" left join (select cv_id,group_concat((tag) separator ',') as tag from cv_tag group by cv_id) j on a.cv_id=j.cv_id ");
		sql.append(" ) comcv ");
		List values = new ArrayList();
		
		sql.append(" where comcv.jobId=? ");
		values.add(query.getJobId());
		values.add(query.getJobId());
		
		if (isNotEmpty(query.getCvId())){
			sql.append(" and comcv.cvId = ? ");
			values.add(query.getCvId());	
		}
		
		//System.out.println("mysql:"+sql.toString());
		
		//return super.getJdbcDao().createPage(query, values, mapper, pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber()); 
	}
	
	public Page findPage(ContractJobCvInterviewQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		
		    Class[] os={ContractJobCvInterviewQuery.class};
    		Class property= org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
        if (property!=null){
        	 if ("java.lang.String".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(),query.getQuery());
	    			
	    		}
	    		else if ("java.lang.Integer".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));
	    			
	    		}
	    		else if ("java.lang.Double".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
	    		}
	    		else if ("java.util.Date".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
	    		}
        }	 
        
        //生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
        StringBuilder sql2 = new StringBuilder("select t from ContractJobCvInterview t where 1=1 ");
          if(isNotEmpty(query.getId())) {
            sql2.append(" and  t.id = :id ");
        }
          if(isNotEmpty(query.getRecomId())) {
            sql2.append(" and  t.recomId = :recomId ");
        }
        if(isNotEmpty(query.getPlanDtBegin())) {
            sql2.append(" and  t.planDt >= :planDtBegin ");
        }
        if(isNotEmpty(query.getPlanDtEnd())) {
            sql2.append(" and  t.planDt <= :planDtEnd ");
        }
        if(isNotEmpty(query.getInterviewer())) {
            sql2.append(" and  t.interviewer like :interviewer ");
            query.setInterviewer("%"+query.getInterviewer()+"%"); 

        }
        if(isNotEmpty(query.getAddress())) {
            sql2.append(" and  t.address like :address ");
            query.setAddress("%"+query.getAddress()+"%"); 

        }
        if(isNotEmpty(query.getContent())) {
            sql2.append(" and  t.content like :content ");
            query.setContent("%"+query.getContent()+"%"); 

        }
        if(isNotEmpty(query.getIvDtBegin())) {
            sql2.append(" and  t.ivDt >= :ivDtBegin ");
        }
        if(isNotEmpty(query.getIvDtEnd())) {
            sql2.append(" and  t.ivDt <= :ivDtEnd ");
        }
          if(isNotEmpty(query.getResult())) {
            sql2.append(" and  t.result = :result ");
        }
        if(isNotEmpty(query.getResultDesc())) {
            sql2.append(" and  t.resultDesc like :resultDesc ");
            query.setResultDesc("%"+query.getResultDesc()+"%"); 

        }
        if(isNotEmpty(query.getCreator())) {
            sql2.append(" and  t.creator like :creator ");
            query.setCreator("%"+query.getCreator()+"%"); 

        }
        if(isNotEmpty(query.getCreateDtBegin())) {
            sql2.append(" and  t.createDt >= :createDtBegin ");
        }
        if(isNotEmpty(query.getCreateDtEnd())) {
            sql2.append(" and  t.createDt <= :createDtEnd ");
        }
        if(isNotEmpty(query.getModifier())) {
            sql2.append(" and  t.modifier like :modifier ");
            query.setModifier("%"+query.getModifier()+"%"); 

        }
        if(isNotEmpty(query.getModifiedDtBegin())) {
            sql2.append(" and  t.modifiedDt >= :modifiedDtBegin ");
        }
        if(isNotEmpty(query.getModifiedDtEnd())) {
            sql2.append(" and  t.modifiedDt <= :modifiedDtEnd ");
        }
        if(isNotEmpty(query.getSortColumns())) {
            sql2.append(" order by :sortColumns ");
        }	
        
		return pageQuery(sql2.toString(),query);
	}
	@Override
	public Page findCvInterviewPage(ContractJobCvInterviewQuery query) {
		//人选姓名，职位，客户职位联系人，面试日期，项目负责人，信息录入人，手机，电邮，客户公司  结果 计划时间
		StringBuffer sql = new StringBuffer();		
		sql.append(" select * from ( ");		
		sql.append(" select a.id,a.cv_id cvId,a.job_id jobId,a.recomId,b.cv_name cvName,a.name jobName,a.resultDesc,a.content," +
				"a.plan_dt planDt,a.iv_dt ivDt,a.a.result,a.interviewer interviewer," +
				"a.interviewer_title interviewerTitle,a.creator,b.mobile,b.tel phone," +
				"b.email,b.company cvCompany ,b.title cvTitle,a.create_dt createDt,a.modified_dt modifiedDt " +
				",b1.abbr_name custName "+
				" from (select distinct a1.id,a1.creator,a1.plan_dt,a1.iv_dt,a1.content,a1.result,a1.result_desc resultDesc,a1.interviewer,a1.interviewer_title,a0.cv_id,a0.job_id,a0.id recomId,a2.name,a2.hr_contacter,a2.ar_contacter,a2.cust_id,a1.create_dt,a1.modified_dt from contract_job_cv_recom a0,contract_job_cv_interview a1,contract_job a2 where a0.id=a1.recom_id and a0.job_id=a2.id) a ");
		
		
		sql.append(" left join  cv b on a.cv_id=b.id and b.is_delete=0 ");
		//customer_contacter
		sql.append(" left join  (select t2.id,t1.name,t1.full_name,t1.abbr_name  from company t1,customer t2 where t1.id=t2.company_id ) b1 on a.cust_id=b1.id ");
		//sql.append(" left join  (select a0.id,a0.cv_name,a1.role,a1.cust_id from cv a0,customer_contacter a1 where a0.id=a1.cv_id ) d on a.cust_id=d.cust_id and d.role='").append(ConstantDic.CUSTOMER_CONTACTER_TYPE_JOB_CONTACTER).append("' ");
		//sql.append(" left join  (select  group_concat((a0.cv_name) separator ',') cv_name,a1.cust_id from cv a0,customer_contacter a1 where a0.id=a1.cv_id  and a1.role='").append(ConstantDic.CUSTOMER_CONTACTER_TYPE_JOB_CONTACTER).append("' group by a1.cust_id   ) d on a.cust_id=d.cust_id ");
		//sql.append(" left join (select  t1.job_id,min(t2.account) account from   contract_job_search_project t1,search_project t2 where t1.project_id=t2.id and t1.job_id=? group by t1.job_id ) e on a.job_id=e.job_id ");		
		sql.append(" ) comcv ");
		List values = new ArrayList();
		
		sql.append(" where 1=1 ");
		
		if(isNotEmpty(query.getJobId())){
			sql.append(" and comcv.jobId = ? ");
			values.add(query.getJobId());
		}
		if (isNotEmpty(query.getCvId())){
			sql.append(" and comcv.cvId = ? ");
			values.add(query.getCvId());	
		}
		
		//System.out.println("mysql:"+sql.toString());
		
		//return super.getJdbcDao().createPage(query, values, mapper, pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber()); 
	}
	

}
